﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Swd05.SH.Bll;

namespace Swd05.SH.Dal
{
	class SqlScriptFactory : Interfaces.IScriptFactory
	{
		private string connectionstring = string.Empty;
		
		public SqlScriptFactory()
		{
		}
		#region Properties
		private string Connectionstring
		{
			get
			{
				if( this.connectionstring.Length == 0 )
				{
					throw new NullReferenceException( "Connectionstring not initialized!" );
				}
				return this.connectionstring;
			}
		}
		#endregion
		#region methods
		public Interfaces.IScriptEngine Create(object context )
		{
			ScriptEngine se = null;

			se = new ScriptEngine(context, this.FindById(context.GetType().FullName));

			return se;
		}
		public void Connect( string connectionstring )
		{
			this.connectionstring = connectionstring;
		}
		public DataTable Load()
		{
			SqlCommand cmd = null;
			SqlDataAdapter da = null;
			DataSet ds = null;
			DataTable dt = null;
			using (SqlConnection conn = new SqlConnection(this.Connectionstring))
			{
				conn.Open();
				cmd = new SqlCommand("SELECT [Id], [Script], [Datum] FROM [dbo].[Scripts] ORDER BY DATUM", conn);

				ds = new DataSet("ds.scripts");

				da = new SqlDataAdapter(cmd);
				da.Fill(ds);

				if (ds.Tables.Count != 1)
				{
					throw new ArgumentException("Ergebnismenge darf nur genau eine Tabelle enthalten!");
				}

				dt = ds.Tables[0];

			}
			return dt;
		}
		public string FindById(string id)
		{
			SqlCommand cmd = null;
			string stream = "";

			if (!this.Exists(id))
			{
				this.Insert(id, "");
			}

			using (SqlConnection conn = new SqlConnection(this.Connectionstring))
			{
				conn.Open();
				cmd = new SqlCommand("SELECT [Script] FROM [dbo].[Scripts] WHERE Id=@Id", conn);
				cmd.Parameters.Add("@Id", SqlDbType.NVarChar, 150);
				cmd.Parameters["@Id"].Value = id;

				stream = (string)cmd.ExecuteScalar();
			}
			return stream;
		}
		public void Commit(string id, string stream)
		{
			if (this.Exists(id))
			{
				if( stream.Length == 0 )
				{
					this.Remove( id );
				}
				else
				{
					this.Update(id, stream);
				}
			}
			else
			{
				this.Insert(id, stream);
			}
		}
		private void Update(string id, string stream)
		{
			SqlCommand cmd = null;
			if (this.Exists(id))
			{
				using (SqlConnection conn = new SqlConnection(this.Connectionstring))
				{
					conn.Open();
					cmd = new SqlCommand("UPDATE [dbo].[Scripts] SET [Script]=@Script, [Datum]=@Datum WHERE Id=@Id", conn);
					cmd.Parameters.Add("@Id", SqlDbType.NVarChar, 150);
					cmd.Parameters.Add("@Script", SqlDbType.NText);
					cmd.Parameters.Add("@Datum", SqlDbType.DateTime);

					cmd.Parameters["@Id"].Value = id;
					cmd.Parameters["@Script"].Value = stream;
					cmd.Parameters["@Datum"].Value = DateTime.Today;

					cmd.ExecuteNonQuery();
				}
			}
		}
		private void Insert(string id, string stream)
		{
			SqlCommand cmd = null;
			if (!this.Exists(id))
			{
				using (SqlConnection conn = new SqlConnection(this.Connectionstring))
				{
					conn.Open();
					cmd = new SqlCommand("INSERT INTO [dbo].[Scripts] ( [Id], [Script], [Datum] ) Values ( @Id, @Script, @Datum )", conn);
					cmd.Parameters.Add("@Id", SqlDbType.NVarChar, 150);
					cmd.Parameters.Add("@Script", SqlDbType.NText);
					cmd.Parameters.Add("@Datum", SqlDbType.DateTime);

					cmd.Parameters["@Id"].Value = id;
					cmd.Parameters["@Script"].Value = stream;
					cmd.Parameters["@Datum"].Value = DateTime.Today.Date;

					cmd.ExecuteNonQuery();
				}
			}
		}
		public void Remove(string id)
		{
			SqlCommand cmd = null;
			using (SqlConnection conn = new SqlConnection(this.Connectionstring))
			{
				conn.Open();
				cmd = new SqlCommand("DELETE FROM [dbo].[Scripts] WHERE Id=@Id", conn);
				cmd.Parameters.Add("@Id", SqlDbType.NVarChar, 150);
				cmd.Parameters["@Id"].Value = id;
				cmd.ExecuteNonQuery();
			}
			return;
		}
		public bool Exists(string id)
		{
			SqlCommand cmd = null;
			int result = -1;
			using (SqlConnection conn = new SqlConnection(this.Connectionstring))
			{
				conn.Open();
				cmd = new SqlCommand("SELECT Count(*) FROM [dbo].[Scripts] WHERE Id=@Id", conn);
				cmd.Parameters.Add("@Id", SqlDbType.NVarChar, 150);
				cmd.Parameters["@Id"].Value = id;

				result = Convert.ToInt32(cmd.ExecuteScalar());
			}
			return (result > 0);
		}
		#endregion
	}
}
